#!/usr/bin/env python

twt_list_user_slug_pairs = [{'user':'latikambourke', 'slug':'politicians'}, {'user':'smh', 'slug':'fairfax-media-journalists'}, {'user':'pinglo', 'slug':'abc-people'}, {'user':'latikambourke', 'slug':'australian-journalists'}, {'user':'latikambourke', 'slug':'media-programs'}, {'user':'latikambourke', 'slug':'authorities'}, {'user':'latikambourke', 'slug':'journalistsoverseas'}, {'user':'latikambourke', 'slug':'interestingbloggers'}, {'user':'latikambourke', 'slug':'ausvotes'}, {'user':'latikambourke', 'slug':'personalities'}, {'user':'latikambourke', 'slug':'lobby-groups'}, {'user':'latikambourke', 'slug':'news'}, {'user':'latikambourke', 'slug':'staffers'}, {'user':'latikambourke', 'slug':'press-gallery'}, {'user':'aujournos', 'slug':'australianjournalists'}, {'user':'ShoebridgeMLC', 'slug':'unions'}, {'user':'jamesmassola', 'slug':'unions'},{'user':'smh', 'slug':'federal-politicians'}, {'user':'carringtonAU', 'slug':'australian-politics'}, {'user':'newscomauHQ', 'slug':'federal-politicians'}, {'user':'kevinperry', 'slug':'politicians'}, {'user':'GhostWhoVotes', 'slug':'aus-independents'}, {'user':'GhostWhoVotes', 'slug':'aus-national-party'}, {'user':'GhostWhoVotes', 'slug':'aus-greens-party'}, {'user':'GhostWhoVotes', 'slug':'aus-minor-parties'}, {'user':'GhostWhoVotes', 'slug':'aus-labor-party'},{'user':'GhostWhoVotes', 'slug':'aus-liberal-party'}, {'user':'GregBarila', 'slug':'australia-journalists'}, {'user':'GreenpeaceAustP', 'slug':'not-for-profits'}, {'user':'GreenpeaceAustP', 'slug':'eco-peeps'}, {'user':'laramcpherson', 'slug':'progress2013'}, {'user':'GreenpeaceAustP', 'slug':'social-change-activists-14'}, {'user':'captaineagle', 'slug':'media-oz'}]


import sys
import os.path
import sqlite3
from twython import Twython
import datetime
from time import sleep
import time

api = Twython(app_key ='', app_secret='', oauth_token= '',  oauth_token_secret= '')

db = 'twitter_lists.sqlite'

def composeTableName(item):

    return(item['user'].replace('-','_') + "_" + item['slug'].replace('-','_'))

def insertUserDb(db, obj, table):

    # Prepare timestamp
    now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    try:
        conn = sqlite3.connect(db)
        cursor = conn.cursor()
        for user in obj:
            sql= "INSERT OR IGNORE INTO %s (id, name, screen_name, location, description, default_profile, default_profile_image, profile_image_url, profile_background_tile, profile_background_image_url, profile_banner_url, profile_sidebar_fill_color, profile_background_color, profile_link_color, profile_text_color, protected, utc_offset, time_zone, url, statuses_count, followers_count, friends_count, favourites_count, geo_enabled, verified, lang, notifications, contributors_enabled, created_at, listed_count, timestamp) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);" % (table, )
            cursor.execute(sql, (user.get('id'), user.get('name'), user.get('screen_name'), user.get('location'), user.get('description'), user.get('default_profile'), user.get('default_profile_image'), user.get('profile_image_url'), user.get('profile_background_tile'), user.get('profile_background_image_url'), user.get('profile_banner_url'), user.get('profile_sidebar_fill_color'), user.get('profile_background_color'), user.get('profile_link_color'), user.get('profile_text_color'), user.get('protected'), user.get('utc_offset'), user.get('time_zone'), user.get('url'), user.get('statuses_count'), user.get('followers_count'), user.get('friends_count'), user.get('favourites_count'), user.get('geo_enabled'), user.get('verified'), user.get('lang'), user.get('notifications'), user.get('contributors_enabled'), user.get('created_at'), user.get('listed_count'), now))
        conn.commit()    

        return

    except Exception,e:
        print str(e)

        return

    
def createDb(db, twt_list_user_slug_pairs):
    conn = sqlite3.connect(db)
    cursor = conn.cursor()

    for item in twt_list_user_slug_pairs:
        print item['slug']
        table_name = composeTableName(item)
        # Create tables
        sql = """
            CREATE TABLE IF NOT EXISTS %s
                (
                id CHAR PRIMARY KEY,
                name CHAR,
                screen_name CHAR,
                location CHAR,
                description CHAR,
                default_profile CHAR,
                default_profile_image CHAR,
                profile_image_url CHAR,
                profile_background_tile CHAR,
                profile_background_image_url CHAR,
                profile_banner_url CHAR,
                profile_sidebar_fill_color CHAR,
                profile_background_color CHAR,
                profile_link_color CHAR,
                profile_text_color CHAR,
                protected CHAR,
                utc_offset CHAR,
                time_zone CHAR,
                url CHAR,
                statuses_count CHAR,
                followers_count CHAR,
                friends_count CHAR,
                favourites_count CHAR,
                geo_enabled CHAR,
                verified CHAR,
                lang CHAR,
                notifications CHAR,
                contributors_enabled CHAR,
                created_at CHAR,
                listed_count CHAR,
                timestamp DATETIME);
            """ % table_name
        cursor.execute(sql)
    conn.commit()

    return


def main(db, twt_list_user_slug_pairs):

    if os.path.isfile(db)==False:
        createDb(db, twt_list_user_slug_pairs)

    for item in twt_list_user_slug_pairs:
        print item['slug']
        result = api.get_list_members(slug=item['slug'], owner_screen_name=item['user'], count=5000)
        insertUserDb(db, result['users'], composeTableName(item))   
    


main(db, twt_list_user_slug_pairs)
